package com.k.jdbc.demo;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.Instant;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.ZonedDateTime;
import java.util.ArrayList;
import java.util.List;
import java.util.TimeZone;
import java.util.UUID;

public class DBUtil {
    private static void close(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    private static void close(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    private static void close(PreparedStatement pst) {
        if (pst != null) {
            try {
                pst.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    private static void close(Statement st) {
        if (st != null) {
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static <T> List<T> mapResultSetToObjectList(ResultSet resultSet, ResultSetMapper<T> mapper) {
        List<T> resultList = new ArrayList<>();
        try {
            while (resultSet.next()) {
                T object = mapper.mapResultSetToObject(resultSet);
                resultList.add(object);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return resultList;
    }

    public interface ResultSetMapper<T> {
        T mapResultSetToObject(ResultSet resultSet) throws SQLException;
    }
    public static Connection getConnection(String driver, String url, String username, String password) throws ClassNotFoundException, SQLException {
        Class.forName(driver);
        Connection conn = DriverManager.getConnection(url, username, password);
        return conn;
    }
    private static List<String> queryDatabaseTableNames(String driver, String url, String username, String password, String tableName){

        String sqlite = "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name; ";
        String mySQL = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'sop'";
        String postgreSQL = "SELECT tablename FROM pg_tables where schemaname = 'public';";
        // select * from user_tables;  or SELECT TABLE_NAME from all_tables where owner='SZYJ'
        String oracleSQL = "SELECT TABLE_NAME from all_tables where owner='SZYJ'";
        String sql =postgreSQL;

        Connection conn = null;
        Statement statement = null;
        ResultSet resultSet = null;
        List<String> list = null;
        try {
            Class.forName(driver);
            conn = getConnection(driver,url,username,password);
            statement = conn.createStatement();
            resultSet = statement.executeQuery(sql);
            while(resultSet.next()){
                String rsname=resultSet.getString(1);
                String rsnameLower = rsname.toLowerCase();
                String[] names = (rsnameLower.trim().split("_"));
                String convertName="";
                //以_截取的串，每个串，首字母大写，如user_role -> UserRole
                for(String name:names){
                    convertName+=name.substring(0, 1).toUpperCase()+name.substring(1) ;
                }
                System.out.println("rsname:"+rsname+" convertName:"+convertName);
//                String content = "<table tableName=\""+rsnameLower+"\" domainObjectName=\""+convertName+"\" "
//                        +"enableCountByExample=\"false\" enableSelectByExample=\"false\" "
//                        +"enableDeleteByExample=\"false\" enableUpdateByExample=\"false\">"
//                        +"<property name=\"useActualColumnNames\" value=\"false\" />"
//                        + "</table>";
//                System.out.println(content);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(resultSet);
            close(statement);
            close(conn);
        }
        return list;
    }

    public static List<String> queryDatabaseTableFields(String driver, String url, String username, String password, String tableName){
        String postgresSql = "SELECT column_name, data_type, is_nullable, column_default FROM information_schema.columns WHERE table_schema = 'public'   AND table_name   =  '"+tableName+"'";
        Connection conn = null;
        Statement statement = null;
        ResultSet resultSet = null;
        List<String> list = null;
        try {
            Class.forName(driver);
            conn = getConnection(driver,url,username,password);
            statement = conn.createStatement();
            resultSet = statement.executeQuery(postgresSql);
            while(resultSet.next()){
                String field = resultSet.getString(1);
                System.out.println(field);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(resultSet);
            close(statement);
            close(conn);
        }
        return list;
    }

    private static List<String> queryDatabaseList(String driver, String url, String username, String password) {
        String sql = "SELECT u.datname  FROM pg_catalog.pg_database u";
        Connection conn = null;
        Statement statement = null;
        ResultSet resultSet = null;
        List<String> list = null;
        try {
            Class.forName(driver);
            conn = getConnection(driver,url,username,password);
            statement = conn.createStatement();
            resultSet = statement.executeQuery(sql);
            list = mapResultSetToObjectList(resultSet, rs -> rs.getString(1));
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(resultSet);
            close(statement);
            close(conn);
        }
        return list;
    }
    private static void testInsert(String driver, String url, String username, String password){
        String sql = "INSERT INTO dt_ttdate(id, ctime1,ctime2,ctime3,ctime4,ctimelong) VALUES(?, ?,?,?,?,?)";
        Connection conn = null;
        PreparedStatement statement = null;
        int result;
//        TimeZone.setDefault(TimeZone.getTimeZone(ZoneId.of("UTC")));
        String insertId = UUID.randomUUID().toString();
        // date
        java.util.Date now = new java.util.Date();
        long timeLong = now.getTime();
        Timestamp nowTs1 =new Timestamp(timeLong);
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, username, password);
            statement = conn.prepareStatement(sql);
            statement.setString(1, insertId);
            statement.setTimestamp(2,nowTs1);
            statement.setTimestamp(3,nowTs1);
            statement.setTimestamp(4,new Timestamp(System.currentTimeMillis()));
            statement.setTimestamp(5,new Timestamp(System.currentTimeMillis()));
            statement.setLong(6,timeLong);
            result = statement.executeUpdate();
            System.out.println(result);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(statement);
            close(conn);
        }
    }
    private static void testGet(String driver, String url, String username, String password){
        String sql = "select * from dt_ttdate";
        Connection conn = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, username, password);
            statement = conn.prepareStatement(sql);

            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                String col1=resultSet.getString(1);
                Date col2=resultSet.getDate(2);
                Date col3=resultSet.getDate(3);
                Date col4 = resultSet.getDate(4);
                Timestamp col5 = resultSet.getTimestamp(5);
                java.util.Date date5 = new java.util.Date(col5.getTime());
                System.out.println(date5);
                Long col6 = resultSet.getLong(6);
                System.out.println("col1:"+col1 + "col2:"+col2 + "col3:"+col3 + "col4:"+col4 + "col5:"+col5+ "col6:"+col6);

            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(resultSet);
            close(statement);
            close(conn);
        }
    }

    public static boolean connectionTest(String driver, String url, String user, String pass) {
        boolean result = true;
        Connection conn = null;
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, user, pass);
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
            result = false;
        } finally {
            close(conn);
        }
        return result;
    }

    private static void initDb(String driver, String url, String username, String password) {
        Connection conn = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, username, password);
            statement = conn.createStatement();
            // 创建数据库信息
            String dbName = "test1108";
            String user = "tom";
            String pass = "123456";
            List<String> sqlList = new ArrayList<>();
            sqlList.add("SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname=" + "'" + dbName + "'" + " AND pid<>pg_backend_pid() " + "\n");
            sqlList.add("drop database if exists " + dbName + "\n");
            sqlList.add("drop ROLE if exists " + user + "\n");
            sqlList.add("CREATE ROLE " + user + " LOGIN ENCRYPTED PASSWORD " + "'" + pass + "'" + " SUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION" + "\n");
            sqlList.add("create database " + dbName + " ENCODING=UTF8" + " TEMPLATE=template0" + "\n");
            for (int i = 0; i < sqlList.size(); i++) {
                statement.execute(sqlList.get(i));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(resultSet);
            close(statement);
            close(conn);
        }
    }

    public static String executeSqlFile(String driver, String url, String user, String pass, String sqlFile) throws Exception {
        Connection con = null;
        Statement statement = null;
        String sqlResults = "";
        FileInputStream fis = null;
        InputStreamReader isr = null;
        BufferedReader br = null;
        if (sqlFile == null) {
            return null;
        }

        File file = new File(sqlFile);
        if (!file.exists() || file.isDirectory()) {
            return null;
        }
        try {
            //数据初始化
            Class.forName(driver);//加载驱动
            //获取数据库连接
            con = DriverManager.getConnection(url, user, pass);
            StringBuilder sb = new StringBuilder();
            fis = new FileInputStream(sqlFile);
            isr = new InputStreamReader(fis, "UTF-8");
            br = new BufferedReader(isr);

            List<String> sqlList = new ArrayList<>();

            String temp = null;
            temp = br.readLine();

            byte[] head = temp.getBytes("UTF-8");
            if (head[0] == -1 && head[1] == -2) {
                System.err.println("SQL文件格式为：UTF-16，不是UTF-8");
                return "file code is UTF-16 ";
            }

            if (head[0] == -2 && head[1] == -1) {
                System.err.println("SQL文件格式为：Unicode，不是UTF-8");
                return "file code is Unicode ";
            }
            //带BOM
            if (head[0] == -17 && head[1] == -69 && head[2] == -65) {
                System.err.println("SQL文件格式为：BOM UTF-8");
                temp = temp.substring(1, temp.length());
            }
            int runtimes = 0;
            while (temp != null) {

//                if (temp.length() >= 2) {
                if (temp.startsWith("#") || temp.startsWith("--") || temp.startsWith("/*") || temp.startsWith("//")) {
                    temp = br.readLine();
                    continue;
                }
                sb.append(temp + "\n");

                if (temp.endsWith(";;;")) {
//                            String sql = sb.toString().replaceAll("\r\n", "\r");
//                            sql = sql.replaceAll("\r", "\n");
                    sqlList.add(sb.toString());
                    sb.delete(0, sb.length());
                    sb.trimToSize();
                }

                    /*if ((sqlList.size() > 0) && (sqlList.size() % batch_insert_count == 0)) {
                        //  logger.warn("sb长度 ：" + sb.length() + ",sqlList=" + sqlList.size());
                        runtimes++;
                        // 执行一条SQL语句
                        sqlResults = executeSql(con, sqlList);
                        sqlList.clear();

                    }*/
//                }
                temp = br.readLine();
            }
            //执行SQL语句
            if (sqlList.size() > 0) {
                // 执行一条SQL语句
                statement = con.createStatement();
                for (int i = 0; i < sqlList.size(); i++) {
                    statement.execute(sqlList.get(i));
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
            sqlResults = sqlResults + e.getMessage();
            throw e;
        } finally {
            if (br != null) {
                br.close();
            }
            if (isr != null) {
                isr.close();
            }
            if (fis != null) {
                fis.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (con != null) {
                con.close();
            }
        }
        return sqlResults;
    }
    private static void testInsertDate(String driver, String url, String username, String password, String tableName) throws ParseException {
        Connection conn = null;
        PreparedStatement statement = null;
        int result;
        String sql = "INSERT INTO ";
        sql+=tableName;
        sql+="(";
        sql+="id,name,date_one,date_two";
        sql+=")";
        sql+="VALUES";
        sql+="(";
        sql+="?,?,?,?";
        sql+=")";
        String insertId = UUID.randomUUID().toString();
        String name = "tom";
        java.util.Date nowDate = new java.util.Date();
//        TimeZone.setDefault(TimeZone.getTimeZone(ZoneId.of("UTC")));
        Timestamp dateOne =new Timestamp(nowDate.getTime());
//        TimeZone.setDefault(TimeZone.getTimeZone(ZoneId.of("Asia/Shanghai")));
//        TimeZone.setDefault(TimeZone.getTimeZone(ZoneId.of("UTC")));
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, username, password);
            statement = conn.prepareStatement(sql);
            statement.setString(1, insertId);
            statement.setString(2,name);
            statement.setTimestamp(3,dateOne);
            statement.setTimestamp(4,dateOne);
            result = statement.executeUpdate();
            System.out.println(result);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(statement);
            close(conn);
        }
    }

    public static void main(String[] args) {
//        String sql1 = "SELECT u.datname  FROM pg_catalog.pg_database u";
//        demo1("org.postgresql.Driver","jdbc:postgresql://10.110.12.33:5432/postgres","postgres","123456",sql1);
        //initDb("org.postgresql.Driver","jdbc:postgresql://10.110.12.33:5432/postgres","postgres","123456");
        try {
//            boolean dbIsConn = connectionTest("org.postgresql.Driver", "jdbc:postgresql://10.110.12.33:5432/test1108", "postgres", "123456");
//            System.out.println("dbConnTest:" + dbIsConn);
//            List<String> dbList = queryDatabaseList("org.postgresql.Driver", "jdbc:postgresql://10.110.12.33:5432/test1108", "postgres", "123456");
//            System.out.println(dbList);
            //executeSqlFile("org.postgresql.Driver","jdbc:postgresql://10.110.12.33:5432/test1108","postgres","123456","D:\\GitRepo\\asbe\\java-jdbc\\src\\main\\java\\com\\k\\jdbc\\demo\\init_Database.sql");
//            queryDatabaseTableFields("org.postgresql.Driver", "jdbc:postgresql://192.168.241.89:5432/gpcs", "postgres", "Command_123","gpcs_early_warning");
//            testGet("org.postgresql.Driver", "jdbc:postgresql://10.110.12.33:5432/test1108", "postgres", "123456");
            // timezone=GMT%2B8
//            TimeZone.setDefault(TimeZone.getTimeZone(ZoneId.of("UTC")));
            testInsertDate("org.postgresql.Driver", "jdbc:postgresql://10.110.12.33:5432/asbe", "postgres", "123456","test_date");
//            TimeZone.setDefault(TimeZone.getTimeZone(ZoneId.of("Asia/Shanghai")));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
